﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.IO;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using DevExpress.Utils;
using System.Threading;

public partial class UserControl_ControlReportDSNgayV2 : System.Web.UI.UserControl
{
    ClassExecute.ClassExecute dt;
    string str = webapp4U.Configuration.CurrentConnectionString;
    string error = "";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (IsPostBack)
        {
            if (Session["db"] != null)
            {
                DataTable db = (DataTable)Session["db"];
                ASPxPivotGrid1.DataSource = db;
                ASPxPivotGrid1.DataBind();
            }
        }
        else
        {
          

            ThreadStart thr = new ThreadStart(GetAdnCodeProduct);
            ThreadStart thr1 = new ThreadStart(LoadMonthLy);
            ThreadStart thr2 = new ThreadStart(LoadDaily);

            Thread tr = new Thread(thr);
            Thread tr1 = new Thread(thr1);
            Thread tr2 = new Thread(thr2);

            tr.Start();
            tr1.Start();
            tr2.Start();

            tr.Join();
            tr1.Join();
            tr2.Join();

        }
    }
    protected void imgBtnAdd_Click(object sender, ImageClickEventArgs e)
    {
        dt = new ClassExecute.ClassExecute();
        try
        {

            DataTable db = dt.Get_Data_DataTable(str, ref error, "k_ReportDSNgayV2", CommandType.StoredProcedure,
                     new SqlParameter("@Username", Session["user"].ToString()),
                     new SqlParameter("@YyyyMm", DropMonthy.SelectedValue.ToString()),
                     new SqlParameter("@FilterDaily",chFilterDaily.Checked),
                     new SqlParameter("@FromDate",DropFrom.SelectedItem.ToString()),
                     new SqlParameter("@Todate",DropTo.SelectedItem.ToString()));
                 
            
            if (db.Rows.Count > 0)
            {


                ASPxPivotGrid1.DataSource = db;

                Session["db"] = db;

            }
            else
            {
                if (Session["db"] != null)
                {
                    Session["db"] = null;
                }
                ASPxPivotGrid1.DataSource = null;
                ShowMessageBox("Dữ liệu không có");
            }
            ASPxPivotGrid1.DataBind();
            //RadioButtonList1.SelectedValue = "0";
            //ArrangeReport();
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    protected void imgBtnSave_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            using (MemoryStream filestream = new MemoryStream())
            {

                ASPxPivotGridExporter1.OptionsPrint.PrintFilterHeaders = DefaultBoolean.False;
                ASPxPivotGridExporter1.OptionsPrint.PrintColumnHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintRowHeaders = DefaultBoolean.True;
                ASPxPivotGridExporter1.OptionsPrint.PrintDataHeaders = DefaultBoolean.False;

                string contentType = "application/ms-excel";

                string fileName = "BaoCaoDoanhSoNgay" + DateTime.Now.ToString("yyyyMMdd") + ".xls";
                ASPxPivotGridExporter1.ExportToXls(filestream);
                byte[] buffer = filestream.GetBuffer();
                Response.Clear();
                Response.Buffer = false;
                Response.AppendHeader("Content-Type", contentType);
                Response.AppendHeader("Content-Transfer-Encoding", "binary");
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
                Response.BinaryWrite(buffer);
                Response.End();
            }
        }
        catch
        {

        }
    }
    void ShowMessageBox(string text)
    {
        MessageBox.Text = text;
        MessageBox.ShowOnPageLoad = true;
    }
    protected void imgBtnRefresh_Click(object sender, ImageClickEventArgs e)
    {
        for (int i = 0; i < ASPxPivotGrid1.Fields.Count; i++)
        {
            ASPxPivotGrid1.Fields[i].FilterValues.Clear();
        }
    }
    void LoadMonthLy()
    {
        dt = new ClassExecute.ClassExecute();
        try
        {

            DataTable db = dt.Get_Data_DataTable(str, ref error, "k_getMonthly", CommandType.StoredProcedure);
            dt.BindDropDownList(db, DropMonthy, "ID", "Des");
        }
        catch (Exception ex)
        {

            string method = System.Reflection.MethodBase.GetCurrentMethod().Name;
            dt.WriteErrorLog(Server.MapPath(webapp4U.Globals.LogUrl()), ex.Message, method);
        }
    }
    void LoadDaily()
    {
        for (int i = 1; i < 32;i++ )
        {
            if (i < 10)
            {
                DropFrom.Items.Add("0" + i.ToString());
                DropTo.Items.Add("0" + i.ToString());
               
            }
            else
            {
                DropFrom.Items.Add(i.ToString());
                DropTo.Items.Add(i.ToString());
            }
        }
    }
    void GetAdnCodeProduct()
    {
        dt = new ClassExecute.ClassExecute();
        if (ViewState["ADNCodeProduct"] == null)
        {
            ViewState["ADNCodeProduct"] = dt.GetDataDepend(str, "k_getADNCodeProduct", CommandType.StoredProcedure, ref error,
                new SqlParameter("@Username", Session["user"].ToString()));
        }
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
    void generatePivotExcel()
    {

        string fileName = DateTime.Now.ToString("ddMMyyyy") + "_" + Session["user"].ToString() + "_DoanhSoNgay.xls";

        if (File.Exists(Server.MapPath(fileName)))
        {
            File.Delete(Server.MapPath(fileName));
        }


        string connection = @"OLEDB;Provider=SQLOLEDB.1;" + str;


        string cmd = "SELECT     * FROM view_dml_show_BCDS_InvoiceDetail" + Session["user"].ToString() + " where commodkey in " + ViewState["ADNCodeProduct"].ToString();
        Excel.Application excel = new Excel.Application();
        try
        {
            excel.Visible = false;
            excel.AlertBeforeOverwriting = false;
            object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };
            Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
            style.Font.Bold = true;
            style.Font.Size = 32;
            sheet.Name = "BaoCaoDoanhSoNgay";
            sheet.Activate();

            foreach (Excel.Worksheet sh in workbook.Worksheets)
            {
                if (sh.Name.ToString() != "BaoCaoDoanhSoNgay")
                {
                    excel.DisplayAlerts = false;
                    sh.Delete();
                }
            }

            Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);

            pivotCache.Connection = connection;
            pivotCache.MaintainConnection = true;




            pivotCache.CommandText = cmd;
            pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
            Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
            Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[22, 2], "PivotTable11", Missing.Value, Missing.Value);


            //, TinhThanh, QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField1 = (Excel.PivotField)pivotTable.PivotFields("KHO");
            pageField1.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField1.Caption = "Kho";


            //, , QuanHuyen, MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField2 = (Excel.PivotField)pivotTable.PivotFields("TenHangTheoNPP");
            pageField2.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField2.Caption = "Tên hàng theo NPP";


            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField30 = (Excel.PivotField)pivotTable.PivotFields("TinhDoanhSoCho");
            pageField30.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField30.Caption = "Tính doanh số cho";




            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField4 = (Excel.PivotField)pivotTable.PivotFields("TenHangTheoFimex");
            pageField4.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField4.Caption = "Tên hàng theo Fimex";


            //SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField5 = (Excel.PivotField)pivotTable.PivotFields("ChannelName");
            pageField5.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField5.Caption = "Kênh";


            //, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField6 = (Excel.PivotField)pivotTable.PivotFields("SubChannelName");
            pageField6.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField6.Caption = "Sub Chanel";


            //, , ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField06 = (Excel.PivotField)pivotTable.PivotFields("sitename");
            pageField06.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
            pageField06.Caption = "Nhà phân phối";



            //, , , , , Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField9 = (Excel.PivotField)pivotTable.PivotFields("TDV");
            pageField9.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField9.Caption = "Trình dược viên bán hàng";


            //, , , , , , MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField10 = (Excel.PivotField)pivotTable.PivotFields("TENKH");
            pageField10.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField10.Caption = "Tên khách hàng";


            //, , , , , , , GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField11 = (Excel.PivotField)pivotTable.PivotFields("DC");
            pageField11.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField11.Caption = "Địa chỉ";


            // GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField12 = (Excel.PivotField)pivotTable.PivotFields("QHT");
            pageField12.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField12.Caption = "Tỉnh/Thành";

            //, , , MaHangTheoNPP, Chanel, SubChanel, NhaPhanPhoi, ASM, TeamLeader, TrinhDuocVien, Customer, MaHang, GAM, GroupSKU, SoLuong, DSoTruocThue, DSoSauThue
            Excel.PivotField pageField3 = (Excel.PivotField)pivotTable.PivotFields("QuanHuyen");
            pageField3.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField3.Caption = "Quận huyện";


            // , , , 
            Excel.PivotField pageField13 = (Excel.PivotField)pivotTable.PivotFields("GroupSKU");
            pageField13.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField13.Caption = "Group";

            Excel.PivotField pageField24 = (Excel.PivotField)pivotTable.PivotFields("GAM");
            pageField24.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField24.Caption = "Gam hàng";

            Excel.PivotField pageField29 = (Excel.PivotField)pivotTable.PivotFields("LAND");
            pageField29.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField29.Caption = "Vùng";

            Excel.PivotField pageField28 = (Excel.PivotField)pivotTable.PivotFields("DGLE");
            pageField28.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField28.Caption = "Đơn giá";

            Excel.PivotField pageField27 = (Excel.PivotField)pivotTable.PivotFields("trntyp");
            pageField27.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField27.Caption = "Loại đơn hàng";

            Excel.PivotField pageField26 = (Excel.PivotField)pivotTable.PivotFields("NGAY");
            pageField26.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField26.Caption = "Ngày";

            Excel.PivotField pageField25 = (Excel.PivotField)pivotTable.PivotFields("INVOICE");
            pageField25.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
            pageField25.Caption = "Số chứng từ";




            pivotTable.AddDataField(pivotTable.PivotFields("SoLuong"), "Số lượng", Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("DSoTruocThue"), "Trước thuế", Excel.XlConsolidationFunction.xlSum);

            pivotTable.AddDataField(pivotTable.PivotFields("DSoSauThue"), "Sau thuế", Excel.XlConsolidationFunction.xlSum);
            pivotTable.RowGrand = false;
            pivotTable.ColumnGrand = true;
            pivotTable.DataPivotField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
            Excel.Range rg = pivotTable.DataBodyRange;
            rg.NumberFormat = "#,##0.00";

            sheet.Cells.EntireColumn.AutoFit();

            Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
            range.Style = style;
            range.Value2 = "BÁO CÁO DOANH SỐ NGÀY";

            Excel.Range range2 = ((Excel.Range)sheet.Cells[2, 1]);
            range2.Value2 = DropMonthy.SelectedItem.ToString();

            Excel.Range range1 = ((Excel.Range)sheet.Cells[3, 1]);
            range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();



            workbook.SaveAs(Server.MapPath(fileName), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            workbook.Close(true, Missing.Value, Missing.Value);
            excel.Quit();

            saveFile(this.Response, Server.MapPath(fileName));

        }
        catch
        {
           
        }
        finally
        {
            // Make sure we release the reference to the underlying COM object
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            excel = null;
        }
    }
    protected void imgBtnPivot_Click(object sender, ImageClickEventArgs e)
    {
        generatePivotExcel();
    }
}